from pymysql import connect, cursors
import configparser as cparser
from pymysql.err import OperationalError
from config import setting
import os
import sys
sys.path.append(os.path.dirname(os.path.dirname(__file__)))


#  读取config.ini文件信息
cf = cparser.ConfigParser()
cf.read(setting.TEST_CONFIG, encoding='utf-8')
host = cf.get("mysqlconfig", "host")
port = cf.get("mysqlconfig", "port")
user = cf.get("mysqlconfig", "user")
password = cf.get("mysqlconfig", "password")
db = cf.get("mysqlconfig", "db_name")


class Mysql_DB:
    def __init__(self):
        try:
            # 连接数据库
            self.conn = connect(host=host, port=3306, user=user, password=password,
                                db=db, charset='utf8mb4')
            self.cursor = self.conn.cursor()
        except OperationalError as e:
            print(f'Mysql error:{e.args[0],e.args[1]} ')

    # 创建表(不支持or replace)
    def create_table(self, table_name, table_field):
        for key in table_field:
            # 空白键拼接 字段名 类型
            table_field[key] = key + " " + table_field[key]
        fields = ",".join(table_field.values())
        # print(fields)
        real_sql = "create table " + table_name + \
            " (" + fields + ");"
        self.cursor.execute(real_sql)
        # create alter drop 内部隐藏commit：self.conn.commit()

    # 新增表字段
    def alter_table_add(self, table_name, new_field_name):
        real_sql = "alter table " + table_name + " add column " + new_field_name + ";"
        self.cursor.execute(real_sql)

    # 修改表字段名称
    def alter_table_rename(self, table_name, old_field_name, new_field_name):
        real_sql = "alter table " + table_name + " rename column " + \
            old_field_name + " to " + new_field_name + ";"
        self.cursor.execute(real_sql)
    # 修改表字段属性

    def alter_table_modify(self, table_name, field_name, new_type):
        real_sql = "alter table " + table_name + " modify " + \
            field_name + new_type + ";"
        self.cursor.execute(real_sql)

    # 删除表字段
    def alter_table_drop(self, table_name, field_name):
        real_sql = "alter table " + table_name + " drop column " + field_name + ";"
        self.cursor.execute(real_sql)

    # 删除表结构
    def drop_table(self, table_name):
        real_sql = "drop table " + table_name + ";"
        self.cursor.execute(real_sql)

    # 新增数据方法

    def insert(self, table_name, table_data):
        for key in table_data:
            table_data[key] = "'" + str(table_data[key]) + "'"
        key = ','.join(table_data.keys())
        value = ','.join(table_data.values())
        real_sql = "insert into " + table_name + \
            '(' + key + ')' + " values " + '(' + value + ')' + ';'

        self.cursor.execute(real_sql)
        self.conn.commit()

    # 查询数据方法
    def select(self, table_name):
        real_sql = "select * from " + table_name + ";"
        # with self.conn.cursor() as cursor:
        self.cursor.execute(real_sql)
        # 打印结果
        print(f'查询结果如下：\n{self.cursor.fetchall()}')

    # 查询特地表记录某个字段值
    def select_key(self, table_name, field_value, key_word):
        for key, value in key_word.items():
            print(key, value)

        real_sql = "select " + field_value + " from " + table_name + \
            " where " + key + " like " + "'%" + value + "%'" + ";"
        print(real_sql)
        self.cursor.execute(real_sql)
        # 打印结果
        for result in self.cursor.fetchall():
            print(f'查询结果如下：\n{result}')
    # 修改数据方法

    def update(self, table_name, old_table_data, new_table_data):
        for key in old_table_data:
            old_table_data[key] = "'" + str(old_table_data[key]) + "'"
        for key in new_table_data:
            new_table_data[key] = "'" + str(new_table_data[key]) + "'"
        old_key = ','.join(old_table_data.keys())
        old_value = ','.join(old_table_data.values())
        new_key = ','.join(new_table_data.keys())
        new_value = ','.join(new_table_data.values())
        # print(new_key, new_value, old_key, old_value)
        real_sql = "update " + table_name + " set " + new_key + " = " + \
            new_value + " where " + old_key + " = " + old_value + ";"
        self.cursor.execute(real_sql)
        self.conn.commit()

    # 删除数据方法
    def delete(self, table_name):
        real_sql = 'delete from ' + table_name + ';'
        # 取消表的键约束
        self.cursor.execute("SET FOREIGN_KEY_CHECKS=0;")
        self.cursor.execute(real_sql)
        self.conn.commit()
    # 清空表数据，自增重新开始

    def truncate(self, table_name):
        real_sql = "truncate table " + table_name + ";"
        self.cursor.execute(real_sql)

    # 初始化数据
    # def init_data(self):
    #     for table, data in TEST_DATA.datas.items():
    #         self.clear(table)
    #         for d in data:
    #             self.insert(table, d)
    #     self.close()
    # 存储过程批量造数
    def create_procedure(self, procedure_name, num):
        real_sql = "delimiter $$\n" + \
            "create procedure " + procedure_name + "()\n" + \
            "begin\n" + \
            "declare i int default 1;\n" + \
            "label:while i<= " + num + " do\n" + \
            "-- 替换开始区域 \n" + \
            "INSERT INTO `test_pymysql`(`id`) \n" + \
            "VALUES (2000001+i);\n" + \
            "-- 替换结束区域 \n" + \
            "set i=i+1;\n" + \
            "end while label;\n" + \
            "end $$\n" + \
            "delimiter ;"
        self.cursor.execute(real_sql)

    # 执行存储过程
    def call_procedure(self, procedure_name):
        real_sql = "call " + procedure_name + "();"
        self.cursor.execute(real_sql)

    # 删除存储过程
    def drop_procedure(self, procedure_name):
        real_sql = "drop procedure if exists " + procedure_name + ";"
        self.cursor.execute(real_sql)

    # 关闭数据库连接
    def close(self):
        self.cursor.close()
        self.conn.close()


if __name__ == "__main__":
    # 调试
    db = Mysql_DB()
    # 创建表
    # table_field = {'id': 'INT PRIMARY KEY', 'name': 'VARCHAR(100)'}
    # table_name = 'test_00001'
    # db.create_table(table_name, table_field)
    # 删除表结构
    # db.drop_table(table_name)
    # # 新增
    # table_data1 = {'id': 3, 'name': 'liyang3'}
    # db.insert('test_pymysql', table_data1)
    # # 更新
    # table_data2 = {'name': 'liyang3'}
    # table_data2_new = {'name': 'liyang3_new'}
    # db.update('test_pymysql', table_data2, table_data2_new)
    # # 删除数据
    # db.delete('test_pymysql')
    # # 查询
    # db.select('test_pymysql')
    db.select_key('test_pymysql', 'id', {'id': "3"})
    procedure_name = 'test01'
    db.create_procedure(procedure_name, '10')
    db.call_procedure(procedure_name)
